package tt.dz.appserver.dao.base;

import java.lang.reflect.Field;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.collections.map.ListOrderedMap;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;

import tt.dz.appserver.util.Constants;
import tt.dz.appserver.util.PageModel;
import tt.dz.appserver.util.ReflectUtils;
import tt.dz.appserver.util.annotation.AnnotationUtils;
import tt.dz.appserver.util.jdbc.JdbcUtils;

/**
 * 数据库基础操作类
 * @author 谢龙飞
 */
@SuppressWarnings({"unchecked","rawtypes"})
public class BasisDao<T>{
	
	/**log4j*/
	private final static Logger LOG = Logger.getLogger("[MyJDBCFrameWork]:");
	
	/**JDBC模板注入*/
	@Resource(name="appJdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
	/***
	 * 保存对象，通用
	 * @param obj
	 * @return
	 */
	public int save(final T obj){
		
		//得到类信息
		final Class<?> clss = obj.getClass();
		//获取属性
		final Field fields[] = clss.getDeclaredFields();
		
		//获取映射表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(clss);

		//参数值集合
		final List<Object> fieldValuesList = new ArrayList<Object>();
		
		//拼接插入语句
		StringBuffer sql = new StringBuffer("INSERT INTO ");
		sql.append(tableName);
		final StringBuffer fieldName = new StringBuffer(" (");
		final StringBuffer fieldValues = new StringBuffer("VALUES (");
		try {
			for (Field field : fields) {
				
				//判断是否含有不可用修饰符 //是否为忽略映射属性
				if(!ReflectUtils.judgeModifiersAvailable(field) && !AnnotationUtils.ignore(field)){

					//调用get方法获取值
					//Object fieldObj = ReflectUtils.invokeObjectReadMethodByFieldNameAndClass(obj, field.getName(), clss);
					
					//改为直接获取
					field.setAccessible(true);
					Object fieldObj = field.get(obj);
					
					if(fieldObj!=null){

						fieldName.append(field.getName());
						fieldName.append(',');
						
						//设置插入字段占位
						fieldValues.append('?');
						fieldValues.append(',');
						//当前位的值有序加入list
						fieldValuesList.add(fieldObj);
					}
				}
			}

			fieldName.deleteCharAt(fieldName.length()-1);
			fieldValues.deleteCharAt(fieldValues.length()-1);
			fieldName.append(") ");
			fieldValues.append(')');
			fieldValues.append(';');
			sql.append(fieldName);
			sql.append(fieldValues);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if(Constants.Jdbc.show_sql){
			LOG.info(sql);
			LOG.info("[param]:"+fieldValuesList);
		}
		return getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				try {
					//有序设置参数集合的参数值
					int size = fieldValuesList.size();
					for (int i = 0; i < size; i++) {
						ps.setObject(i+1, fieldValuesList.get(i));
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}
	
	/***
	 * 保存对象，通用
	 * @param obj
	 * @return
	 */
	public int saveAll(final List<T> obj){
		
		if(obj != null){
			
			try {
				//拼接插入语句
				StringBuffer sql = new StringBuffer("INSERT INTO ");
				//属性
				final StringBuffer fieldName = new StringBuffer(" (");
				//插入值
				final StringBuffer fieldValues = new StringBuffer(" VALUES ");

				boolean firstAppend = true;
				
				//类信息
				Class<?> clss = null;
				
				String tableName = null;

				//参数值集合
				final List<Object> fieldValuesList = new ArrayList<Object>();
				
				for (Object object : obj) {
					//得到类信息
					if(clss==null){
						clss = object.getClass();
						//获取映射表名称
						tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(clss);
						sql.append(tableName);
					}
					//获取属性
					final Field fields[] = clss.getDeclaredFields();
					
					fieldValues.append('(');
					for (Field field : fields) {
						
						//判断是否含有不可用修饰符 //是否为忽略映射属性
						if(!ReflectUtils.judgeModifiersAvailable(field) && !AnnotationUtils.ignore(field)){

							//改为直接获取
							field.setAccessible(true);
							Object fieldObj = field.get(object);
							
							if(fieldObj!=null){
								//拼接一次属性就行
								if(firstAppend){
									fieldName.append(field.getName());
									fieldName.append(',');
								}
								
								//设置插入字段占位
								fieldValues.append('?');
								fieldValues.append(',');
								//当前位的值有序加入list
								fieldValuesList.add(fieldObj);
							}
						}
					}
					fieldValues.deleteCharAt(fieldValues.length()-1);
					firstAppend = false;
					fieldValues.append("), ");
				}
				fieldName.deleteCharAt(fieldName.length()-1);
				fieldName.append(')');
				fieldValues.replace(fieldValues.length()-2, fieldValues.length()-1, ";");
				sql.append(fieldName);
				sql.append(fieldValues);

				if(Constants.Jdbc.show_sql){
					LOG.info(sql);
					LOG.info("[param]:"+fieldValuesList);
				}

				return getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
					@Override
					public void setValues(PreparedStatement ps) throws SQLException {
						try {
							//有序设置参数集合的参数值
							int size = fieldValuesList.size();
							for (int i = 0; i < size; i++) {
								ps.setObject(i+1, fieldValuesList.get(i));
							}
						} catch (Exception e) {
							e.printStackTrace();
						}
					}
				});
			} catch (DataAccessException | IllegalArgumentException | IllegalAccessException e) {
				e.printStackTrace();
				return Constants.Jdbc.EDIT_ERROR;
			}
		}
		else{
			return Constants.Jdbc.EDIT_ERROR;
		}
	}
	
	
	/**
	 * 逻辑删除信息,通用
	 * @param obj
	 */
	public int LogicDelete(final T obj){
		
		//得到类信息
		final Class<?> cls = obj.getClass();
		
		//得到表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);

		//参数值集合
		final List<Object> fieldValuesList = new ArrayList<Object>();
		
		//开始拼接sql
		final StringBuffer sql = new StringBuffer("UPDATE ");
		sql.append(tableName);
		
		final Field[] fields = cls.getDeclaredFields();
		
		sql.append(" SET ");
		sql.append(Constants.Jdbc.VALID_FLAG);
		sql.append("=? ");
		boolean firstAppend = true;
		
		//主键个数
		boolean haveKey = false;
		try {
			for (Field field : fields) {

				//判断是否含有不可用修饰符           //是否跳过此属性        //是否为主键  
				if(!ReflectUtils.judgeModifiersAvailable(field) && 
						!AnnotationUtils.ignore(field) && AnnotationUtils.fieldIsPrimarkey(field)){
					haveKey=true;
					//改为直接获取
					field.setAccessible(true);
					Object fieldObj = field.get(obj);
					
					if(fieldObj==null){
						throw new IllegalArgumentException("单个对象删除所有主键列不可为空");
					}
					
					if(firstAppend){
						firstAppend=false;
						sql.append(" WHERE ");
					}
					else{
						sql.append(" AND ");
					}
					sql.append(field.getName());
					//拼接字段占位
					sql.append("=? ");
					//当前位的值有序加入list
					fieldValuesList.add(fieldObj);
				}
			}
			if(!haveKey){
				throw new IllegalStateException("没有主键,不可进行此操作");
			}
		} catch (Exception e) {
			e.printStackTrace();
			return Constants.Jdbc.EDIT_ERROR;
		}

		if(Constants.Jdbc.show_sql){
			LOG.info(sql);
			LOG.info("[param]:"+fieldValuesList);
		}		

		return getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter()  {

			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				try {
					//有序设置参数集合的参数值
					ps.setObject(1, 0);
					int size = fieldValuesList.size();
					for (int i = 0; i < size; i++) {
						ps.setObject(i+2, fieldValuesList.get(i));
	 				}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}
	
	/**
	 * 修改信息，通用
	 * @param obj
	 * @return
	 */
	public int update(final T obj){
		
		//得到对象类信息
		final Class<?> cls = obj.getClass();
		
		//获取表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		
		final Field[] fields = cls.getDeclaredFields();

		//参数值集合
		final List<Object> fieldValuesList = new ArrayList<Object>();	
		//where条件参数集合
		final List<Object> whereValuesList = new ArrayList<Object>();		
		//拼接修改语句
		StringBuffer mainSql = new StringBuffer("UPDATE ");
		//拼接修改条件
		StringBuffer whereSql = new StringBuffer();
		mainSql.append(tableName);
		mainSql.append(" SET ");
		//首次拼接
		boolean firstAppend = true;
		//主键个数
		boolean haveKey = false;
		
		try {
			for (Field field : fields) {

				//判断是否含有不可用修饰符 //是否为忽略映射属性
				if(!ReflectUtils.judgeModifiersAvailable(field) && !AnnotationUtils.ignore(field)){
					
					//改为直接获取
					field.setAccessible(true);
					Object fieldObj = field.get(obj);
					
					//如果返回值为不为空代表有值，拼接update
					if(fieldObj!=null && !AnnotationUtils.fieldIsPrimarkey(field)){
						String fieldName = field.getName();
						mainSql.append(fieldName);
						//拼接字段占位
						mainSql.append("=");
						mainSql.append('?');
						mainSql.append(',');
						//当前位的值有序加入list
						fieldValuesList.add(fieldObj);
					}
				}
				//如果为主键列
				if(AnnotationUtils.fieldIsPrimarkey(field)){
					haveKey = true;
					//改为直接获取
					field.setAccessible(true);
					Object fieldObj = field.get(obj);
					
					if(fieldObj==null){
						throw new IllegalArgumentException("单对象修改所有主键列不可为空");
					}
					else{
						if(firstAppend){
							firstAppend=false;
							whereSql.append(" WHERE ");
						}
						else{
							whereSql.append(" AND ");
						}
						whereSql.append(field.getName());

						//拼接字段占位
						whereSql.append('=');
						whereSql.append('?');
						//当前位的值有序加入list
						whereValuesList.add(fieldObj);
					}
				}
			}
			if(!haveKey){
				throw new IllegalStateException("没有主键,不可进行此操作");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		mainSql.deleteCharAt(mainSql.length()-1);
		
		if(Constants.Jdbc.show_sql){
			LOG.info(mainSql.append(whereSql));
			LOG.info("[param]:"+fieldValuesList+whereValuesList);
		}
		return getJdbcTemplate().update(mainSql.toString(), new PreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				try {
					
					//有序设置修改值参数集合的参数值
					int size = fieldValuesList.size();
					for (int i = 0; i < size; i++) {
						ps.setObject(i+1, fieldValuesList.get(i));
					}
					
					//where参数集
					int whereSize = whereValuesList.size();
					for (int i = 0; i < whereSize; i++) {
						ps.setObject(i+1+size, whereValuesList.get(i));
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}
	
	/**
	 * 物理删除，通用
	 * @param obj
	 * @return
	 */
	public int delete(final T obj){
		
		//得到对象类信息
		final Class<?> cls = obj.getClass();
		
		//获取表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);
		
		final Field[] fields = cls.getDeclaredFields();

		//where条件参数集合
		final List<Object> whereValuesList = new ArrayList<Object>();		
		//拼接修改语句
		StringBuffer mainSql = new StringBuffer("DELETE FROM ");

		
		mainSql.append(tableName);
		//首次拼接
		boolean firstAppend = true;
		//主键个数
		boolean haveKey = false;
		
		try {
			for (Field field : fields) {
				//如果为主键列
				if(AnnotationUtils.fieldIsPrimarkey(field)){
					haveKey = true;
					//改为直接获取
					field.setAccessible(true);
					Object fieldObj = field.get(obj);
					
					if(fieldObj==null){
						throw new IllegalArgumentException("单对象物理删除所有主键列不可为空");
					}
					else{
						if(firstAppend){
							firstAppend=false;
							mainSql.append(" WHERE ");
						}
						else{
							mainSql.append(" AND ");
						}
						mainSql.append(field.getName());

						//拼接字段占位
						mainSql.append('=');
						mainSql.append('?');
						//当前位的值有序加入list
						whereValuesList.add(fieldObj);
					}
				}
			}
			if(!haveKey){
				throw new IllegalStateException("没有主键,不可进行此操作");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if(Constants.Jdbc.show_sql){
			LOG.info(mainSql);
			LOG.info("[param]:"+whereValuesList);
		}
		return getJdbcTemplate().update(mainSql.toString(), new PreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				try {
					//where参数集
					int whereSize = whereValuesList.size();
					for (int i = 0; i < whereSize; i++) {
						ps.setObject(i+1, whereValuesList.get(i));
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}
	
	/**
	 * 通用查询方法
	 * @param cls 类
	 * @param paramsMap 有序参数Map
	 * @param pageNo 页码索引
	 * @param pageSize 每页个数
	 * @param sortName 排序列名
	 * @param sortVal 排序方式 1 正序 其他倒序
	 * @return
	 */
	public List<T> findListAutomaticOfClass(final Class<T> cls, final ListOrderedMap paramsMap,Integer pageNo,
			final Integer pageSize, final String sortName, final Integer sortVal){

		//获取表名称
		String tableName = AnnotationUtils.getEntityTableMappingByAnnotationName(cls);

		//开始拼接sql
		StringBuffer sqlBuffer = new StringBuffer("SELECT ");
		
		final Field[] fields = cls.getDeclaredFields();

		//设置查询列
		sqlBuffer.append(ReflectUtils.setQueryColumn(fields, tableName));
		
		sqlBuffer.append(" FROM ");
		sqlBuffer.append(tableName);

		boolean firstAppend = true;		

		//循环拼接引用查询的映射列条件（链接查询条件）
		if(paramsMap!=null){
			int paramsSize = paramsMap.size();
			for(int i=0;i<paramsSize;i++){
				if(firstAppend){
					firstAppend = false;
					sqlBuffer.append(" WHERE ");
				}
				else{
					sqlBuffer.append(" AND ");
				}
				//tableName（主表）表的条件
				sqlBuffer.append(paramsMap.get(i));
				sqlBuffer.append('=');
				sqlBuffer.append('?');
			}
		}
		
		//排序匹配
		if(StringUtils.isNotEmpty(sortName)){
			sqlBuffer.append(" ORDER BY ");
			//tableName（主表）表的排序条件
			sqlBuffer.append(sortName);
			if(sortVal==1){
				sqlBuffer.append(" ASC");
			}
			else{
				sqlBuffer.append(" DESC");
			}
		}
		
		//分页查询匹配
		if(pageNo!=null && pageSize!=null && pageSize>0){
			sqlBuffer.append(" LIMIT ");
			sqlBuffer.append((pageNo-1)*pageSize);
			sqlBuffer.append(',');
			sqlBuffer.append(pageSize);
		}

		if(Constants.Jdbc.show_sql){
			LOG.info(sqlBuffer);
			LOG.info("[param]:"+paramsMap);
		}
		
		List<T> resultList = getJdbcTemplate().query(sqlBuffer.toString(),new PreparedStatementSetter(){
		    @Override
		    public void setValues(PreparedStatement ps) throws SQLException {
		    	if(paramsMap!=null){
					//有序设置参数集合的参数值
					int paramsSize = paramsMap.size();
			    	for(int i=0;i<paramsSize;i++){
						ps.setObject(i+1, paramsMap.getValue(i));
					}
		    	}
		    }
	    }, new RowMapper<T>() {
			@Override
			public T mapRow(ResultSet rs, int arg1) throws SQLException {
				return JdbcUtils.setColumn(rs, cls, fields);
			}
		});
		return resultList;
	}
	
	/**
	 * 对象查询
	 * @param obj
	 * @return
	 */
	public List<T> findExample(final T obj){
		
		return this.findExample(obj, null, null, null, null);
	}
	
	/**
	 * 通过对象查询匹配数据
	 * @param pageNo 页码 第一页开始
	 * @param pageSize 每页个数
	 * @param sortName 排序列名
	 * @param sortVal 排序方式 1 正序 其他倒序
	 * @return
	 */
	public List<T> findExample(final T obj,Integer pageNo,
			final Integer pageSize, final String sortName, final Integer sortVal){
		
		//得到类信息
		final Class<T> cls = (Class<T>) obj.getClass();
		//所有属性
		final Field fields[] = cls.getDeclaredFields();
		//参数值集合
		final List<Object> queryParamList = new ArrayList<Object>();
		
		//生成sql
		String sql = JdbcUtils.generateSql(obj, fields, cls, sortName, sortVal, pageNo, pageSize, queryParamList);
		
		if(Constants.Jdbc.show_sql){
			LOG.info(sql);
			LOG.info("[param]:"+queryParamList);
		}
		return getJdbcTemplate().query(sql, new PreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				
				int paramCount = queryParamList.size();
				//设置参数
				for (int i=0;i<paramCount;i++) {
					Object obj = queryParamList.get(i);
					ps.setObject(i+1, obj);
				}
			}
		}, new RowMapper<T>() {

			@Override
			public T mapRow(ResultSet rs, int arg1) throws SQLException {
				return JdbcUtils.setColumn(rs, cls, fields);
			}
		});
	}
	
	/**
	 * 通过sql查询数据返回List集合
	 * @param sql
	 * @param params
	 * @return
	 */
	public List<Map<String,Object>> findDataOfMapBySQL(StringBuffer sql,final Integer pageNo,
			final Integer pageSize,final Object ...params){
		
		if(pageNo!=null && pageSize!=null && pageSize>0){
			sql.append(" LIMIT ");
			sql.append((pageNo-1)*pageSize);
			sql.append(',');
			sql.append(pageSize);
		}
		
		if(Constants.Jdbc.show_sql){
			LOG.info(sql);
			LOG.info("[param]:"+Arrays.asList(params));
		}
		
		return getJdbcTemplate().query(sql.toString(), new PreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				
				if(params!=null && params.length>0){
					//设置参数
					int paramsSize = params.length;
					for (int i=0;i<paramsSize;i++) {
						ps.setObject(i+1, params[i]);
					}
				}
			}
		},new RowMapper<Map<String,Object>>(){

			@Override
			public Map<String,Object> mapRow(ResultSet rs, int arg1) throws SQLException {
				
				ResultSetMetaData resultSetMetaData = rs.getMetaData();
				Map<String,Object> map = new HashMap<String,Object>();
				
				//title个数
				int columnCount = resultSetMetaData.getColumnCount();
				for (int i = 0; i < columnCount; i++) {
					//封装结果集
					String columnName = resultSetMetaData.getColumnName(i+1);
					if(!map.containsKey(columnName)){
						map.put(columnName, rs.getObject(columnName));
					}
				}
				return map;
			}
		});
	}
	
	/**
	 * 通过sql查询封装至实体
	 * @param sql
	 * @param cls
	 * @param params
	 * @return
	 */
	public List<T> findDataAutomaticOfClassBySQL(StringBuffer sql,final Class<T> cls,
			final Integer pageNo,final Integer pageSize, final Object ...params){
		
		
		if(pageNo!=null && pageSize!=null && pageSize>0){
			sql.append(" LIMIT ");
			sql.append((pageNo-1)*pageSize);
			sql.append(',');
			sql.append(pageSize);
		}
		if(Constants.Jdbc.show_sql){
			LOG.info(sql);
			LOG.info("[param]:"+Arrays.asList(params));
		}
		return getJdbcTemplate().query(sql.toString(), new PreparedStatementSetter() {
			
			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				
				//设置参数
				if(params!=null){
					int paramLength = params.length;
					for (int i=0;i<paramLength;i++) {
						Object obj = params[i];
						ps.setObject(i+1, obj);
					}
				}
			}
		},new RowMapper<T>() {

			@Override
			public T mapRow(ResultSet rs, int arg1) throws SQLException {
				return JdbcUtils.setColumn(rs, cls, cls.getDeclaredFields());
			}
		});
	}
	
	/**
	 * 分页查询通用
	 * @param sql
	 * @param pageNo
	 * @param pageSize
	 * @param paramsMap
	 * @param sortName
	 * @param sortVal
	 * @return
	 */
	public PageModel pageingQueryBySQL(String sql, Class<T> cls, Integer pageNo, Integer pageSize, Object...params){

		long start = System.currentTimeMillis();
		//得到sql
		StringBuffer countBuffer = new StringBuffer(sql);
		int fromIndex = countBuffer.indexOf("FROM") & countBuffer.indexOf("from");
		if(fromIndex==-1){
			throw new IllegalStateException("SQL查询语句出现问题,无\"FROM\"关键字.");
		}
		//拼接个数查询
		countBuffer.delete(0, fromIndex-1);
		countBuffer.insert(0, "SELECT COUNT(*) AS totalCount");

		int orderByIndex = countBuffer.indexOf("LIMIT") & countBuffer.indexOf("limit");
		if(orderByIndex!=-1){
			countBuffer.delete(orderByIndex,countBuffer.length());
		}
		
		orderByIndex = countBuffer.indexOf("ORDER") & countBuffer.indexOf("order");
		if(orderByIndex!=-1){
			countBuffer.delete(orderByIndex,countBuffer.length());
		}

		//得到sql的数据条数
		List<Map<String,Object>> countMapList = this.findDataOfMapBySQL(countBuffer, null, null, params);

		//总个数
		int totalCount = Integer.parseInt(countMapList.get(0).get("totalCount").toString());
		
		//页数也每页个数
		pageSize = (pageSize==null || pageSize<1)?20:pageSize;
		
		PageModel pageModel = new PageModel();
		
		//设置每页个数
		pageModel.setPageSize(pageSize);
		
		//设置总个数
		pageModel.setTotalCount(totalCount);
		
		//计算总页数
		pageModel.setTotalPage((int)Math.ceil((double)totalCount/(double)pageSize));

		//页数设置标准
		pageNo = (pageNo==null || pageNo<=1)?1:pageNo;
		pageNo = totalCount<=pageNo?totalCount:pageNo;
		
		pageModel.setPageNo(pageNo);
		pageModel.setCurrentPage(pageNo);

		LOG.info("查询个数用时:"+(System.currentTimeMillis()-start));

		start = System.currentTimeMillis();
		//查询数据集合
		if(cls==null){
			List<Map<String,Object>> dataList = this.findDataOfMapBySQL(new StringBuffer(sql), null, null, params);
			pageModel.setDataList(dataList);
		}
		else{
			//查询数据集合
			List<T> dataList = this.findDataAutomaticOfClassBySQL(new StringBuffer(sql), cls, null, null, params);
			//设置分页数据
			pageModel.setDataList(dataList);
		}

		LOG.info("查询集合用时:"+(System.currentTimeMillis()-start));
		
		return pageModel;
	}
	
	/**
	 * 分页查询通用2
	 * @param sql
	 * @param pageNo
	 * @param pageSize
	 * @param paramsMap
	 * @param sortName
	 * @param sortVal
	 * @return
	 */
	public PageModel<T> pageingQueryExample(final T obj,Integer pageNo,
			final Integer pageSize, final String sortName, final Integer sortVal){
		
		//得到类信息
		final Class<T> cls = (Class<T>) obj.getClass();
		//所有属性
		final Field fields[] = cls.getDeclaredFields();
		//参数值集合
		final List<Object> queryParamList = new ArrayList<Object>();
		
		//得到的sql
		String sql = JdbcUtils.generateSql(obj, fields, cls, sortName, sortVal, pageNo, pageSize, queryParamList);
		
		//list转数组
		Object[] params = queryParamList.toArray();
		
		return this.pageingQueryBySQL(sql, cls, pageNo, pageSize , params);
	}
	
	/**
	 * 执行sql
	 * @param sql
	 * @param params
	 * @return
	 */
	public int executeSQL(String sql,final Object...params){
		
		return getJdbcTemplate().update(sql, new PreparedStatementSetter() {

			@Override
			public void setValues(PreparedStatement ps) throws SQLException {
				try {
					if(params!=null){
						int whereSize = params.length;
						for (int i = 0; i < whereSize; i++) {
							ps.setObject(i+1, params[i]);
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}
}
